package hanqi.mangement.Mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import hanqi.mangement.entity.grade;
import hanqi.mangement.entity.gradebro;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface gradeMapper extends BaseMapper<grade> {
    @Select("SELECT g.id, s.name AS studentname, c.title AS coursename, g.score " +
            "FROM grades g " +
            "JOIN student s ON g.student_id = s.id " +
            "JOIN course c ON g.course_id = c.id")
    List<gradebro> findAllGradesWithNames();

    @Select("SELECT g.id, s.name AS studentname, c.title AS coursename, g.score " +
            "FROM grades g " +
            "JOIN student s ON g.student_id = s.id " +
            "JOIN course c ON g.course_id = c.id " +
            "WHERE g.course_id = #{courseId}")
    List<gradebro> findByCourseId(int courseId);

    @Select("SELECT g.id, s.name AS studentname, c.title AS coursename, g.score " +
            "FROM grades g " +
            "JOIN student s ON g.student_id = s.id " +
            "JOIN course c ON g.course_id = c.id " +
            "WHERE g.score BETWEEN #{minScore} AND #{maxScore}")
    List<gradebro> findByScoreBetween(@Param("minScore") double minScore, @Param("maxScore") double maxScore);
}
